import mysql.connector

try:
    # 连接数据库
    conn = mysql.connector.connect(
        host='localhost',
        user='root',
        password='1472580369',
        database='medical_literature_system'
    )

    cursor = conn.cursor()

    # 检查文献表数据
    cursor.execute("SELECT id, title, upload_user_id, status, create_time FROM literature")
    literature_results = cursor.fetchall()
    print("文献表数据:")
    for row in literature_results:
        print(f"  ID: {row[0]}, 标题: {row[1][:50]}..., 用户ID: {row[2]}, 状态: {row[3]}, 创建时间: {row[4]}")

    # 检查关键词表数据
    cursor.execute("SELECT id, word, category FROM keyword LIMIT 10")
    keyword_results = cursor.fetchall()
    print("\n关键词表数据 (前10条):")
    for row in keyword_results:
        print(f"  ID: {row[0]}, 关键词: {row[1]}, 分类: {row[2]}")

    # 检查文献关键词关联表数据
    cursor.execute("SELECT id, literature_id, keyword_id FROM literature_keyword LIMIT 10")
    lk_results = cursor.fetchall()
    print("\n文献关键词关联表数据 (前10条):")
    for row in lk_results:
        print(f"  ID: {row[0]}, 文献ID: {row[1]}, 关键词ID: {row[2]}")

    # 检查用户ID为1的文献
    cursor.execute("SELECT id, title FROM literature WHERE upload_user_id = 1 AND status = 1")
    user_literature = cursor.fetchall()
    print("\n用户ID为1的文献:")
    for row in user_literature:
        print(f"  ID: {row[0]}, 标题: {row[1][:50]}...")

    # 检查关联数据
    cursor.execute("""
        SELECT k.word, COUNT(lk.id) as count, AVG(lk.weight) as weight, k.category 
        FROM keyword k 
        JOIN literature_keyword lk ON k.id = lk.keyword_id 
        JOIN literature l ON lk.literature_id = l.id 
        WHERE l.status = 1 AND l.upload_user_id = 1
        GROUP BY k.id, k.word, k.category 
        ORDER BY count DESC, weight DESC 
        LIMIT 10
    """)
    join_results = cursor.fetchall()
    print("\n关联查询结果 (前10条):")
    for row in join_results:
        print(f"  关键词: {row[0]}, 数量: {row[1]}, 权重: {row[2]}, 分类: {row[3]}")

    cursor.close()
    conn.close()
    print("\n数据库检查完成")
    
except mysql.connector.Error as err:
    print(f"数据库连接错误: {err}")
except Exception as e:
    print(f"其他错误: {e}")